from flask import Flask,request
from db import get_db

app = Flask(__name__)

@app.route('/')
def index():
    return "hello world"

@app.route('/query')
def query_data():
    db = get_db()
    try:
        with db.cursor() as cursor:
            # 执行查询
            sql = "SELECT * FROM users" # 编写sql语句
            cursor.execute(sql)  # 执行sql语句
            results = cursor.fetchall() # 拿到数据返回的结果
            return {'data': results} # 将数据返回给前端
    finally:
        db.close()

@app.route('/insert', methods=['POST'])
def insert_data():

    username = request.form.get('username')
    email = request.form.get('email')

    db = get_db()
    try:
        with db.cursor() as cursor:
            # 插入数据
            sql = "INSERT INTO users (username, email) VALUES (%s, %s)"
            cursor.execute(sql, (username, email))
            db.commit()  # 提交事务
            return {'message': 'Insert successful'}, 201
    except Exception as e:
        db.rollback()  # 出错时回滚
        return {'error': str(e)}, 500
    finally:
        db.close()

if __name__ == '__main__':
    app.run(debug=True)